﻿using Big_Data.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Big_Data.Data_Base_Excel.Offres_Facteurs_Clés
{
    class Requete
    {
        public string datedebut { get; set; }
        public string datefin { get; set; }
        public string choix_pays { get; set; }
        public string DATES { get; set; }
        public string VALEURS { get; set; }
        public string PAYS { get; set; }

        public int GetIdDate(string ref_date)
        {
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                return (from id in entities.DateSet
                        where id.Année.Equals(ref_date)
                        select id.Id).FirstOrDefault();
            }
        }

        public List<string> getDate()
        {
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                return (from date in entities.DateSet
                        orderby date.Année ascending
                        select date.Année).ToList();
            }
        }
        public List<string> getPays()
        {
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                return (from date in entities.PaysSet
                        orderby date.Name ascending
                        select date.Name).ToList();
            }
        }

        // Select emploi
        public List<Requete> SelectEmploi()
        {
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                string[] splitDateDebut = datedebut.Split('-');
                string[] splitDateFin = datefin.Split('-');

                int debut = Int32.Parse(splitDateDebut[0]);
                int fin = Int32.Parse(splitDateFin[0]);

                return (from emploi in entities.EmploiSet.AsEnumerable()
                        join date in entities.DateSet
                            on emploi.DateId equals date.Id
                        join pay in entities.PaysSet
                            on emploi.PaysId equals pay.Id
                        let split_date = date.Année.Split('-')
                        let convert_date = Int32.Parse(split_date[0])
                        where pay.Name.Equals(choix_pays)
                           && (convert_date >= debut) && (convert_date <= fin)
                           && (!emploi.Valeurs.Contains(".."))
                        select new Requete()
                        {
                            DATES = date.Année,
                            VALEURS = emploi.Valeurs,
                            PAYS = pay.Name
                        }).ToList();
            }
        }
        public List<Requete> SelectPopActive()
        {
            using (Offres_facteursContainer entities = new Offres_facteursContainer())
            {
                string[] splitDateDebut = datedebut.Split('-');
                string[] splitDateFin = datefin.Split('-');

                int debut = Int32.Parse(splitDateDebut[0]);
                int fin = Int32.Parse(splitDateFin[0]);

                return (from pop in entities.Population_ActiveSet.AsEnumerable()
                        join date in entities.DateSet
                            on pop.DateId equals date.Id
                        join pay in entities.PaysSet
                            on pop.PaysId equals pay.Id
                        let split_date = date.Année.Split('-')
                        let convert_date = Int32.Parse(split_date[0])
                        where pay.Name.Equals(choix_pays)
                           && (convert_date >= debut) && (convert_date <= fin)
                           && (!pop.Valeurs.Contains(".."))
                        select new Requete()
                        {
                            DATES = date.Année,
                            VALEURS = pop.Valeurs,
                            PAYS = pay.Name
                        }).ToList();
            }
        } 
    }
}
